package database

import (
	"context"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"time"

	_ "github.com/go-sql-driver/mysql"
	"github.com/pkg/errors"

	"changeme/internal/models"
)

// MySQLDriver MySQL数据库驱动
type MySQLDriver struct {
	BaseDriver
	capabilities DatabaseCapabilities
}

// NewMySQLDriver 创建MySQL驱动实例
func NewMySQLDriver() *MySQLDriver {
	return &MySQLDriver{
		BaseDriver: BaseDriver{
			Dialect: &MySQLDialect{},
		},
		capabilities: &MySQLCapabilities{},
	}
}

// GetCapabilities 获取数据库能力
func (m *MySQLDriver) GetCapabilities() DatabaseCapabilities {
	if m.capabilities == nil {
		m.capabilities = &MySQLCapabilities{}
	}
	return m.capabilities
}

// Connect 连接到MySQL数据库
func (m *MySQLDriver) Connect(config *models.DatabaseConfig) error {
	if m.Connected {
		return ErrConnectionExists
	}

	dsn := m.buildDSN(config)
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return errors.Wrap(err, "failed to open mysql connection")
	}

	// 设置连接池参数
	db.SetMaxOpenConns(25)
	db.SetMaxIdleConns(5)
	db.SetConnMaxLifetime(5 * time.Minute)

	// 测试连接
	if err := db.Ping(); err != nil {
		db.Close()
		return errors.Wrap(err, "failed to ping mysql database")
	}

	m.DB = db
	m.Config = config
	m.Connected = true

	return nil
}

// GetVersion 获取MySQL版本
func (m *MySQLDriver) GetVersion() (string, error) {
	if !m.IsConnected() {
		return "", ErrNotConnected
	}

	var version string
	err := m.DB.QueryRow("SELECT VERSION()").Scan(&version)
	if err != nil {
		return "", errors.Wrap(err, "failed to get mysql version")
	}

	return version, nil
}

// ListDatabases 列出所有数据库
func (m *MySQLDriver) ListDatabases(ctx context.Context) ([]models.DatabaseInfo, error) {
	if !m.IsConnected() {
		return nil, ErrNotConnected
	}

	query := `
		SELECT 
			SCHEMA_NAME as name,
			DEFAULT_CHARACTER_SET_NAME as charset,
			DEFAULT_COLLATION_NAME as collation,
			'' as comment
		FROM information_schema.SCHEMATA 
		WHERE SCHEMA_NAME NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
		ORDER BY SCHEMA_NAME
	`

	rows, err := m.DB.QueryContext(ctx, query)
	if err != nil {
		return nil, errors.Wrap(err, "failed to list databases")
	}
	defer rows.Close()

	var databases []models.DatabaseInfo
	for rows.Next() {
		var db models.DatabaseInfo
		if err := rows.Scan(&db.Name, &db.Charset, &db.Collation, &db.Comment); err != nil {
			return nil, errors.Wrap(err, "failed to scan database info")
		}
		databases = append(databases, db)
	}

	return databases, nil
}

// ListTables 列出指定数据库的所有表
func (m *MySQLDriver) ListTables(ctx context.Context, database string) ([]models.TableInfo, error) {
	if !m.IsConnected() {
		return nil, ErrNotConnected
	}

	query := `
		SELECT 
			TABLE_NAME as name,
			TABLE_SCHEMA as schema_name,
			TABLE_TYPE as type,
			ENGINE as engine,
			TABLE_COLLATION as collation,
			IFNULL(TABLE_ROWS, 0) as row_count,
			IFNULL(DATA_LENGTH, 0) as data_length,
			IFNULL(INDEX_LENGTH, 0) as index_length,
			IFNULL(TABLE_COMMENT, '') as comment,
			IFNULL(CREATE_TIME, '') as created_at
		FROM information_schema.TABLES 
		WHERE TABLE_SCHEMA = ?
		ORDER BY TABLE_NAME
	`

	rows, err := m.DB.QueryContext(ctx, query, database)
	if err != nil {
		return nil, errors.Wrap(err, "failed to list tables")
	}
	defer rows.Close()

	var tables []models.TableInfo
	for rows.Next() {
		var table models.TableInfo
		var createdAt sql.NullString
		if err := rows.Scan(
			&table.Name, &table.Schema, &table.Type, &table.Engine,
			&table.Collation, &table.RowCount, &table.DataLength,
			&table.IndexLength, &table.Comment, &createdAt,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan table info")
		}
		if createdAt.Valid {
			table.CreatedAt = createdAt.String
		}
		tables = append(tables, table)
	}

	return tables, nil
}

// GetTableColumns 获取表的列信息
func (m *MySQLDriver) GetTableColumns(ctx context.Context, database, table string) ([]models.ColumnInfo, error) {
	if !m.IsConnected() {
		return nil, ErrNotConnected
	}

	query := `
		SELECT 
			COLUMN_NAME,
			DATA_TYPE,
			COLUMN_TYPE,
			IFNULL(CHARACTER_MAXIMUM_LENGTH, 0) as char_length,
			IFNULL(NUMERIC_PRECISION, 0) as num_precision,
			IFNULL(NUMERIC_SCALE, 0) as num_scale,
			IS_NULLABLE,
			COLUMN_KEY,
			COLUMN_DEFAULT,
			EXTRA,
			COLUMN_COMMENT,
			ORDINAL_POSITION,
			CHARACTER_SET_NAME,
			COLLATION_NAME
		FROM information_schema.COLUMNS 
		WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
		ORDER BY ORDINAL_POSITION
	`

	rows, err := m.DB.QueryContext(ctx, query, database, table)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table columns")
	}
	defer rows.Close()

	var columns []models.ColumnInfo
	for rows.Next() {
		var column models.ColumnInfo
		var columnName, dataType, columnType, isNullable, columnKey, columnDefault sql.NullString
		var charLength, numPrecision, numScale, position sql.NullInt64
		var extra, comment, charSet, collation sql.NullString

		// 扫描所有字段
		if err := rows.Scan(
			&columnName, &dataType, &columnType, &charLength, &numPrecision,
			&numScale, &isNullable, &columnKey, &columnDefault, &extra,
			&comment, &position, &charSet, &collation,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan column info")
		}

		// 处理枚举和集合类型的值
		var enumValues []string
		if (dataType.String == "enum" || dataType.String == "set") && columnType.Valid {
			// 从COLUMN_TYPE中提取枚举值，例如: enum('value1','value2')
			valuesStr := columnType.String
			// 移除类型名称和括号
			valuesStr = strings.TrimPrefix(valuesStr, dataType.String+"(")
			valuesStr = strings.TrimSuffix(valuesStr, ")")
			// 按逗号分割并处理引号
			for _, val := range strings.Split(valuesStr, ",") {
				// 移除引号
				cleanVal := strings.Trim(val, "'\"")
				enumValues = append(enumValues, cleanVal)
			}
		}

		// 设置列信息
		column.Name = columnName.String
		column.Type = strings.ToLower(dataType.String)
		column.FullType = columnType.String

		// 处理长度、精度和小数位
		if charLength.Valid {
			column.Length = int(charLength.Int64)
		}
		if numPrecision.Valid {
			column.Precision = int(numPrecision.Int64)
		}
		if numScale.Valid {
			column.Scale = int(numScale.Int64)
		}

		// 处理NULL约束
		column.IsNullable = isNullable.String == "YES"
		column.NotNull = !column.IsNullable

		// 处理主键
		column.IsPrimaryKey = columnKey.String == "PRI"
		column.PrimaryKey = column.IsPrimaryKey

		// 处理自增
		column.IsAutoIncrement = strings.Contains(strings.ToLower(extra.String), "auto_increment")
		column.AutoIncrement = column.IsAutoIncrement

		// 处理默认值
		if columnDefault.Valid {
			column.DefaultValue = columnDefault.String
		}

		// 其他元数据
		column.Comment = comment.String
		column.Position = int(position.Int64)
		column.Charset = charSet.String
		column.Collation = collation.String
		column.Extra = extra.String
		column.Values = enumValues

		// 创建一个结构体存储原始信息，便于前端使用或调试
		column.RawInfo = map[string]interface{}{
			"columnName":    columnName.String,
			"dataType":      dataType.String,
			"columnType":    columnType.String,
			"isNullable":    isNullable.String,
			"columnKey":     columnKey.String,
			"columnDefault": columnDefault.Valid,
			"defaultValue":  columnDefault.String,
			"extra":         extra.String,
		}

		columns = append(columns, column)
	}

	// 使用格式化工具处理列信息
	return FormatColumns(columns, models.MySQL), nil
}

// GetTableIndexes 获取表的索引信息
func (m *MySQLDriver) GetTableIndexes(ctx context.Context, database, table string) ([]models.IndexInfo, error) {
	if !m.IsConnected() {
		return nil, ErrNotConnected
	}

	query := `
		SELECT 
			INDEX_NAME as name,
			INDEX_TYPE as type,
			IF(NON_UNIQUE = 0, 1, 0) as is_unique,
			IF(INDEX_NAME = 'PRIMARY', 1, 0) as is_primary,
			GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) as columns,
			IFNULL(INDEX_COMMENT, '') as comment
		FROM information_schema.STATISTICS 
		WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
		GROUP BY INDEX_NAME, INDEX_TYPE, NON_UNIQUE, INDEX_COMMENT
		ORDER BY INDEX_NAME
	`

	rows, err := m.DB.QueryContext(ctx, query, database, table)
	if err != nil {
		return nil, errors.Wrap(err, "failed to get table indexes")
	}
	defer rows.Close()

	var indexes []models.IndexInfo
	for rows.Next() {
		var index models.IndexInfo
		var columnsStr string
		if err := rows.Scan(
			&index.Name, &index.Type, &index.IsUnique,
			&index.IsPrimary, &columnsStr, &index.Comment,
		); err != nil {
			return nil, errors.Wrap(err, "failed to scan index info")
		}
		index.Columns = strings.Split(columnsStr, ",")
		indexes = append(indexes, index)
	}

	return indexes, nil
}

// ExecuteQuery 执行查询语句
func (m *MySQLDriver) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	if !m.IsConnected() {
		return nil, ErrNotConnected
	}

	start := time.Now()
	rows, err := m.DB.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	// 获取列名
	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 获取列类型
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	// 读取数据
	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 尝试确定数据类型
					if colTypes != nil && i < len(colTypes) {
						// 检查数据库列类型
						dbType := strings.ToLower(colTypes[i].DatabaseTypeName())

						// 对于BIGINT类型，转换为字符串以避免JS中的整数溢出
						if strings.Contains(dbType, "bigint") {
							row[col] = string(b)
							continue
						}

						// 对于DECIMAL/NUMERIC类型，也转换为字符串保留精度
						if strings.Contains(dbType, "decimal") || strings.Contains(dbType, "numeric") {
							row[col] = string(b)
							continue
						}
					}

					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 检查是否为int64类型，如果是则转换为字符串
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						// 超出JavaScript安全整数范围
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

// ExecuteNonQuery 执行非查询语句
func (m *MySQLDriver) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	if !m.IsConnected() {
		return 0, ErrNotConnected
	}

	result, err := m.DB.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows")
	}

	return affected, nil
}

// BeginTransaction 开始事务
func (m *MySQLDriver) BeginTransaction(ctx context.Context) (DatabaseTransaction, error) {
	if !m.IsConnected() {
		return nil, ErrNotConnected
	}

	tx, err := m.DB.BeginTx(ctx, nil)
	if err != nil {
		return nil, errors.Wrap(err, "failed to begin transaction")
	}

	return &MySQLTransaction{tx: tx}, nil
}

// buildDSN 构建MySQL连接字符串
func (m *MySQLDriver) buildDSN(config *models.DatabaseConfig) string {
	// 不指定具体数据库，连接到MySQL服务器
	dsn := fmt.Sprintf("%s:%s@tcp(%s:%d)/",
		config.Username, config.Password, config.Host, config.Port)

	params := []string{}
	if config.Charset != "" {
		params = append(params, "charset="+config.Charset)
	} else {
		params = append(params, "charset=utf8mb4")
	}

	if config.Timezone != "" {
		params = append(params, "loc="+config.Timezone)
	}

	// 添加默认参数
	params = append(params, "parseTime=true")
	params = append(params, "multiStatements=true")

	if config.Options != "" {
		params = append(params, config.Options)
	}

	if len(params) > 0 {
		dsn += "?" + strings.Join(params, "&")
	}

	// 记录DSN（隐藏密码）
	logDSN := fmt.Sprintf("%s:***@tcp(%s:%d)/",
		config.Username, config.Host, config.Port)
	if len(params) > 0 {
		logDSN += "?" + strings.Join(params, "&")
	}

	// 使用全局logger或创建一个临时logger
	// 这里我们简单地使用fmt.Printf，在生产环境中应该使用proper logger
	fmt.Printf("MySQL DSN: %s\n", logDSN)
	fmt.Printf("Password length: %d\n", len(config.Password))

	return dsn
}

// MySQLTransaction MySQL事务实现
type MySQLTransaction struct {
	tx *sql.Tx
}

func (mt *MySQLTransaction) Commit() error {
	return mt.tx.Commit()
}

func (mt *MySQLTransaction) Rollback() error {
	return mt.tx.Rollback()
}

func (mt *MySQLTransaction) ExecuteQuery(ctx context.Context, query string, args ...interface{}) (*models.QueryResult, error) {
	start := time.Now()
	rows, err := mt.tx.QueryContext(ctx, query, args...)
	if err != nil {
		return &models.QueryResult{
			Error:       err.Error(),
			ExecuteTime: time.Since(start),
		}, nil
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get columns")
	}

	// 获取列类型
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		return nil, errors.Wrap(err, "failed to get column types")
	}

	var result []map[string]interface{}
	for rows.Next() {
		values := make([]interface{}, len(columns))
		valuePtrs := make([]interface{}, len(columns))
		for i := range values {
			valuePtrs[i] = &values[i]
		}

		if err := rows.Scan(valuePtrs...); err != nil {
			return nil, errors.Wrap(err, "failed to scan row")
		}

		row := make(map[string]interface{})
		for i, col := range columns {
			val := values[i]

			// 处理特殊类型
			if val != nil {
				// 处理byte数组
				if b, ok := val.([]byte); ok {
					// 尝试确定数据类型
					if colTypes != nil && i < len(colTypes) {
						// 检查数据库列类型
						dbType := strings.ToLower(colTypes[i].DatabaseTypeName())

						// 对于BIGINT类型，转换为字符串以避免JS中的整数溢出
						if strings.Contains(dbType, "bigint") {
							row[col] = string(b)
							continue
						}

						// 对于DECIMAL/NUMERIC类型，也转换为字符串保留精度
						if strings.Contains(dbType, "decimal") || strings.Contains(dbType, "numeric") {
							row[col] = string(b)
							continue
						}
					}

					// 默认转换为字符串
					row[col] = string(b)
				} else {
					// 检查是否为int64类型，如果是则转换为字符串
					if i64, ok := val.(int64); ok && (i64 > 9007199254740991 || i64 < -9007199254740991) {
						// 超出JavaScript安全整数范围
						row[col] = strconv.FormatInt(i64, 10)
					} else {
						row[col] = val
					}
				}
			} else {
				row[col] = nil
			}
		}
		result = append(result, row)
	}

	return &models.QueryResult{
		Columns:     columns,
		Rows:        result,
		RowCount:    len(result),
		ExecuteTime: time.Since(start),
	}, nil
}

func (mt *MySQLTransaction) ExecuteNonQuery(ctx context.Context, query string, args ...interface{}) (int64, error) {
	result, err := mt.tx.ExecContext(ctx, query, args...)
	if err != nil {
		return 0, errors.Wrap(err, "failed to execute non-query in transaction")
	}

	affected, err := result.RowsAffected()
	if err != nil {
		return 0, errors.Wrap(err, "failed to get affected rows in transaction")
	}

	return affected, nil
}

// MySQLDialect MySQL SQL方言
type MySQLDialect struct{}

func (d *MySQLDialect) QuoteIdentifier(identifier string) string {
	return "`" + strings.ReplaceAll(identifier, "`", "``") + "`"
}

func (d *MySQLDialect) QuoteString(str string) string {
	return "'" + strings.ReplaceAll(str, "'", "''") + "'"
}

func (d *MySQLDialect) GetDataTypeMapping() map[string]string {
	return map[string]string{
		"varchar":   "VARCHAR",
		"char":      "CHAR",
		"text":      "TEXT",
		"int":       "INT",
		"bigint":    "BIGINT",
		"decimal":   "DECIMAL",
		"float":     "FLOAT",
		"double":    "DOUBLE",
		"datetime":  "DATETIME",
		"timestamp": "TIMESTAMP",
		"date":      "DATE",
		"time":      "TIME",
		"boolean":   "BOOLEAN",
		"json":      "JSON",
	}
}

func (d *MySQLDialect) GetLimitClause(limit, offset int) string {
	if offset > 0 {
		return fmt.Sprintf("LIMIT %d OFFSET %d", limit, offset)
	}
	return fmt.Sprintf("LIMIT %d", limit)
}

func (d *MySQLDialect) GetCurrentTimeFunction() string {
	return "NOW()"
}

func (d *MySQLDialect) GetAutoIncrementClause() string {
	return "AUTO_INCREMENT"
}

// 表设计相关方法

// GenerateCreateTableSQL 生成创建表SQL
func (m *MySQLDriver) GenerateCreateTableSQL(database string, tableSchema *models.TableSchema) (string, error) {
	var sql strings.Builder

	// 表名
	tableName := m.formatTableIdentifier(database, tableSchema.Name)
	sql.WriteString(fmt.Sprintf("CREATE TABLE %s (\n", tableName))

	// 列定义
	columnDefs := []string{}
	primaryKeys := []string{}

	for _, col := range tableSchema.Columns {
		colDef := m.generateColumnDefinition(col)
		columnDefs = append(columnDefs, colDef)

		if col.PrimaryKey {
			primaryKeys = append(primaryKeys, m.Dialect.QuoteIdentifier(col.Name))
		}
	}

	// 添加列定义
	sql.WriteString("  " + strings.Join(columnDefs, ",\n  "))

	// 添加主键约束
	if len(primaryKeys) > 0 {
		sql.WriteString(",\n  PRIMARY KEY (" + strings.Join(primaryKeys, ", ") + ")")
	}

	// 添加索引
	for _, idx := range tableSchema.Indexes {
		if idx.Type == "PRIMARY" {
			continue // 主键已处理
		}

		indexDef := m.generateIndexDefinition(idx)
		sql.WriteString(",\n  " + indexDef)
	}

	// 添加外键
	for _, fk := range tableSchema.ForeignKeys {
		fkDef := m.generateForeignKeyDefinition(fk)
		sql.WriteString(",\n  " + fkDef)
	}

	sql.WriteString("\n)")

	// 表选项
	if tableSchema.Engine != "" {
		sql.WriteString(" ENGINE=" + tableSchema.Engine)
	}
	if tableSchema.Charset != "" {
		sql.WriteString(" DEFAULT CHARSET=" + tableSchema.Charset)
	}
	if tableSchema.Collation != "" {
		sql.WriteString(" COLLATE=" + tableSchema.Collation)
	}
	if tableSchema.Comment != "" {
		sql.WriteString(" COMMENT=" + m.Dialect.QuoteString(tableSchema.Comment))
	}

	sql.WriteString(";")

	return sql.String(), nil
}

// generateColumnDefinition 生成列定义
func (m *MySQLDriver) generateColumnDefinition(col models.ColumnSchema) string {
	var def strings.Builder

	// 列名
	def.WriteString(m.Dialect.QuoteIdentifier(col.Name))
	def.WriteString(" ")

	// 数据类型
	def.WriteString(strings.ToUpper(col.Type))

	// 长度/精度
	if col.Length > 0 {
		if col.Scale > 0 {
			def.WriteString(fmt.Sprintf("(%d,%d)", col.Length, col.Scale))
		} else {
			def.WriteString(fmt.Sprintf("(%d)", col.Length))
		}
	} else if col.Precision > 0 {
		if col.Scale > 0 {
			def.WriteString(fmt.Sprintf("(%d,%d)", col.Precision, col.Scale))
		} else {
			def.WriteString(fmt.Sprintf("(%d)", col.Precision))
		}
	} else if len(col.Values) > 0 && (strings.ToUpper(col.Type) == "ENUM" || strings.ToUpper(col.Type) == "SET") {
		// 枚举值
		quotedValues := make([]string, len(col.Values))
		for i, v := range col.Values {
			quotedValues[i] = m.Dialect.QuoteString(v)
		}
		def.WriteString("(" + strings.Join(quotedValues, ",") + ")")
	}

	// 字符集和排序规则
	if col.Charset != "" {
		def.WriteString(" CHARACTER SET " + col.Charset)
	}
	if col.Collation != "" {
		def.WriteString(" COLLATE " + col.Collation)
	}

	// NOT NULL
	if col.NotNull {
		def.WriteString(" NOT NULL")
	}

	// 默认值
	if col.DefaultValue != "" {
		if strings.ToUpper(col.DefaultValue) == "NULL" {
			def.WriteString(" DEFAULT NULL")
		} else if strings.ToUpper(col.DefaultValue) == "CURRENT_TIMESTAMP" {
			def.WriteString(" DEFAULT CURRENT_TIMESTAMP")
		} else {
			def.WriteString(" DEFAULT " + m.Dialect.QuoteString(col.DefaultValue))
		}
	}

	// 自增
	if col.AutoIncrement {
		def.WriteString(" AUTO_INCREMENT")
	}

	// 注释
	if col.Comment != "" {
		def.WriteString(" COMMENT " + m.Dialect.QuoteString(col.Comment))
	}

	return def.String()
}

// generateIndexDefinition 生成索引定义
func (m *MySQLDriver) generateIndexDefinition(idx models.IndexSchema) string {
	var def strings.Builder

	if strings.ToUpper(idx.Type) == "UNIQUE" {
		def.WriteString("UNIQUE KEY ")
	} else {
		def.WriteString("KEY ")
	}

	def.WriteString(m.Dialect.QuoteIdentifier(idx.Name))
	def.WriteString(" (")

	quotedCols := make([]string, len(idx.Columns))
	for i, col := range idx.Columns {
		quotedCols[i] = m.Dialect.QuoteIdentifier(col)
	}
	def.WriteString(strings.Join(quotedCols, ", "))
	def.WriteString(")")

	if idx.Comment != "" {
		def.WriteString(" COMMENT " + m.Dialect.QuoteString(idx.Comment))
	}

	return def.String()
}

// generateForeignKeyDefinition 生成外键定义
func (m *MySQLDriver) generateForeignKeyDefinition(fk models.ForeignKeySchema) string {
	var def strings.Builder

	def.WriteString("CONSTRAINT ")
	def.WriteString(m.Dialect.QuoteIdentifier(fk.Name))
	def.WriteString(" FOREIGN KEY (")

	quotedCols := make([]string, len(fk.Columns))
	for i, col := range fk.Columns {
		quotedCols[i] = m.Dialect.QuoteIdentifier(col)
	}
	def.WriteString(strings.Join(quotedCols, ", "))
	def.WriteString(") REFERENCES ")

	def.WriteString(m.Dialect.QuoteIdentifier(fk.ReferenceTable))
	def.WriteString(" (")

	quotedRefCols := make([]string, len(fk.ReferenceColumns))
	for i, col := range fk.ReferenceColumns {
		quotedRefCols[i] = m.Dialect.QuoteIdentifier(col)
	}
	def.WriteString(strings.Join(quotedRefCols, ", "))
	def.WriteString(")")

	if fk.UpdateRule != "" {
		def.WriteString(" ON UPDATE " + fk.UpdateRule)
	}
	if fk.DeleteRule != "" {
		def.WriteString(" ON DELETE " + fk.DeleteRule)
	}

	return def.String()
}

// GenerateAlterTableSQL 生成修改表SQL
func (m *MySQLDriver) GenerateAlterTableSQL(database, table string, alterSchema *models.TableAlterSchema) ([]string, error) {
	sqls := []string{}
	tableName := m.formatTableIdentifier(database, table)

	// 处理列操作
	if len(alterSchema.DropColumns) > 0 {
		for _, col := range alterSchema.DropColumns {
			sql := fmt.Sprintf("ALTER TABLE %s DROP COLUMN %s", tableName, m.Dialect.QuoteIdentifier(col))
			sqls = append(sqls, sql)
		}
	}

	if len(alterSchema.AddColumns) > 0 {
		for _, col := range alterSchema.AddColumns {
			colDef := m.generateColumnDefinition(col)
			sql := fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s", tableName, colDef)
			sqls = append(sqls, sql)
		}
	}

	if len(alterSchema.ModifyColumns) > 0 {
		for _, col := range alterSchema.ModifyColumns {
			colDef := m.generateColumnDefinition(col)
			sql := fmt.Sprintf("ALTER TABLE %s MODIFY COLUMN %s", tableName, colDef)
			sqls = append(sqls, sql)
		}
	}

	if len(alterSchema.RenameColumns) > 0 {
		for oldName, newName := range alterSchema.RenameColumns {
			sql := fmt.Sprintf("ALTER TABLE %s RENAME COLUMN %s TO %s",
				tableName,
				m.Dialect.QuoteIdentifier(oldName),
				m.Dialect.QuoteIdentifier(newName))
			sqls = append(sqls, sql)
		}
	}

	// 处理索引操作
	if len(alterSchema.DropIndexes) > 0 {
		for _, idx := range alterSchema.DropIndexes {
			sql := fmt.Sprintf("ALTER TABLE %s DROP INDEX %s", tableName, m.Dialect.QuoteIdentifier(idx))
			sqls = append(sqls, sql)
		}
	}

	if len(alterSchema.AddIndexes) > 0 {
		for _, idx := range alterSchema.AddIndexes {
			indexDef := m.generateIndexDefinition(idx)
			sql := fmt.Sprintf("ALTER TABLE %s ADD %s", tableName, indexDef)
			sqls = append(sqls, sql)
		}
	}

	// 处理外键操作
	if len(alterSchema.DropForeignKeys) > 0 {
		for _, fk := range alterSchema.DropForeignKeys {
			sql := fmt.Sprintf("ALTER TABLE %s DROP FOREIGN KEY %s", tableName, m.Dialect.QuoteIdentifier(fk))
			sqls = append(sqls, sql)
		}
	}

	if len(alterSchema.AddForeignKeys) > 0 {
		for _, fk := range alterSchema.AddForeignKeys {
			fkDef := m.generateForeignKeyDefinition(fk)
			sql := fmt.Sprintf("ALTER TABLE %s ADD %s", tableName, fkDef)
			sqls = append(sqls, sql)
		}
	}

	// 处理表选项
	var tableOptions []string
	if alterSchema.Engine != "" {
		tableOptions = append(tableOptions, "ENGINE="+alterSchema.Engine)
	}
	if alterSchema.Charset != "" {
		tableOptions = append(tableOptions, "DEFAULT CHARSET="+alterSchema.Charset)
	}
	if alterSchema.Collation != "" {
		tableOptions = append(tableOptions, "COLLATE="+alterSchema.Collation)
	}
	if alterSchema.Comment != "" {
		tableOptions = append(tableOptions, "COMMENT="+m.Dialect.QuoteString(alterSchema.Comment))
	}

	if len(tableOptions) > 0 {
		sql := fmt.Sprintf("ALTER TABLE %s %s", tableName, strings.Join(tableOptions, ", "))
		sqls = append(sqls, sql)
	}

	return sqls, nil
}

// GenerateRenameTableSQL 生成重命名表SQL
func (m *MySQLDriver) GenerateRenameTableSQL(database, oldTable, newTable string) string {
	oldName := m.formatTableIdentifier(database, oldTable)
	newName := m.formatTableIdentifier(database, newTable)
	return fmt.Sprintf("RENAME TABLE %s TO %s", oldName, newName)
}

// GetTableDDL 获取表的DDL语句
func (m *MySQLDriver) GetTableDDL(database, table string) (string, error) {
	query := fmt.Sprintf("SHOW CREATE TABLE %s", m.formatTableIdentifier(database, table))

	var tableName, createSQL string
	err := m.DB.QueryRow(query).Scan(&tableName, &createSQL)
	if err != nil {
		return "", errors.Wrap(err, "failed to get table DDL")
	}

	return createSQL, nil
}

// Exec 实现事务的Exec方法
func (mt *MySQLTransaction) Exec(query string, args ...interface{}) (sql.Result, error) {
	return mt.tx.Exec(query, args...)
}
